using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using Framework.Modules.LinkSite.Library.Data;

namespace Framework.Modules.LinkSite.Library.DataAccess
{
	public class LinkDB
	{
		public static DataTable GetAll(string _lang)
		{
			DataTable retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("LinkSite_Links_GetAll", dbConn);
			dbCmd.Parameters.AddWithValue("@Link_Lang", _lang);
			dbCmd.CommandType = CommandType.StoredProcedure;
			try
			{
				retVal = new DataTable();
				SqlDataAdapter da = new SqlDataAdapter(dbCmd);
				da.Fill(retVal);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal;
		}

		public static void Delete(int _link_ID)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("LinkSite_Links_Delete", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Link_ID", _link_ID);
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}

		public static void SetIndex(int _link_ID, int index)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("LinkSite_Links_SetIndex", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Link_ID", _link_ID);
			dbCmd.Parameters.AddWithValue("@Link_Index", index);
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}

		public static int Insert(LinkInfo _linkInfo)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("LinkSite_Links_Insert", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Link_Name", _linkInfo.Link_Name);
			dbCmd.Parameters.AddWithValue("@Link_Lang", _linkInfo.Link_Lang);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return (int) dbCmd.Parameters["@RETURN_VALUE"].Value;
			}
			finally
			{
				dbConn.Close();
			}
		}

		public static void Update(LinkInfo _linkInfo)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("LinkSite_Links_Update", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Link_ID", _linkInfo.Link_ID);
			dbCmd.Parameters.AddWithValue("@Link_Name", _linkInfo.Link_Name);
			dbCmd.Parameters.AddWithValue("@Link_Target", _linkInfo.Link_Target);
			dbCmd.Parameters.AddWithValue("@Link_Description", _linkInfo.Link_Description);
			dbCmd.Parameters.AddWithValue("@Link_Index", _linkInfo.Link_Index);
			dbCmd.Parameters.AddWithValue("@Link_Lang", _linkInfo.Link_Lang);
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}

		public static LinkInfo GetInfo(int _link_ID)
		{
			LinkInfo retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("LinkSite_Links_GetInfo", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Link_ID", _link_ID);
			try
			{
				dbConn.Open();
				SqlDataReader dr = dbCmd.ExecuteReader();
				if (dr.Read())
				{
					retVal = new LinkInfo();
					retVal.Link_ID = Convert.ToInt32(dr["Link_ID"]);
					retVal.Link_Name = Convert.ToString(dr["Link_Name"]);
					retVal.Link_Target = Convert.ToString(dr["Link_Target"]);
					retVal.Link_Description = Convert.ToString(dr["Link_Description"]);
					retVal.Link_Index = Convert.ToInt32(dr["Link_Index"]);
					retVal.Link_Lang = Convert.ToString(dr["Link_Lang"]);
				}
				if (dr != null) dr.Close();
			}
			finally
			{
				dbConn.Close();
			}
			return retVal;
		}

	}
}